﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using System.Text;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;

namespace hxlxc
{
    // from https://www.cnblogs.com/smile-wei/p/4281265.html
    class MyExcelTools
    {

        public void ExportMultiSheets(List<System.Data.DataTable> dts,
            string exportFileName, List<string> sheetNameList)
        {
            Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Workbooks workbooks = xlApp.Workbooks;
            Workbook workBook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);

            int index = 1;
            // 调用生成sheet的方法
            for (int i = dts.Count - 1; i >= 0; i--)
            {
                GenerateOneSheet(dts[i], workBook, index, sheetNameList[i]);
                index++;
            }

            #region 保存Excel
            workBook.Saved = true;
            //workBook.Save();
            //workBook.SaveCopyAs(filePath);
            workBook.SaveAs(exportFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
                Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            #endregion

            #region 释放Excel资源

            if (workBook != null)
            {
                workBook.Close(false, null, null);
                //System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                workBook = null;
            }
            if (workbooks != null)
            {
                //System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
                workbooks = null;
            }
            if (xlApp != null)
            {
                xlApp.Workbooks.Close();
                //System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                xlApp.Quit();
                xlApp = null;
            }

            // workBook.Close(false, null, null);

            // xlApp.Workbooks.Close();
            // xlApp.Quit();

            GC.WaitForPendingFinalizers();
            GC.Collect();
            #endregion
        }

        // 生成一个Sheet
        private void GenerateOneSheet(System.Data.DataTable dt,
            Workbook workBook, int sheetIndex, string sheetName)
        {


            //取得sheet[i]
            Worksheet workSheet = null;

            if (sheetIndex == 1)
            {
                workSheet = (Worksheet)workBook.Worksheets[sheetIndex];
            }
            else
            {
                workSheet = (Worksheet)workBook.Worksheets.Add(Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value);
            }
            workSheet.Name = sheetName;

            int rowIndex = 1;
            int colIndex = 0;

            System.Data.DataTable table = dt;//ds.Tables[0] ;
            // 生成标题
            foreach (DataColumn col in table.Columns)
            {
                colIndex++;
                workSheet.Cells[1, colIndex] = col.ColumnName;
                // excel.Cells[1, colIndex] = col.ColumnName;
            }

            // 写入数据
            foreach (DataRow row in table.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in table.Columns)
                {
                    colIndex++;
                    workSheet.Cells.NumberFormatLocal = "@";
                    workSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                }
            }

            #region 释放Excel资源
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
            //workSheet = null;
            #endregion

            // System.Diagnostics.Process.Start(exportFileName.ToString());

        }
    }
}
